package com.rttmall.shopbackend.utils;

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.math.BigDecimal;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import org.apache.commons.io.FileUtils;
import org.apache.commons.io.IOUtils;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.RichTextString;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.util.StringUtils;
import org.springframework.web.multipart.commons.CommonsMultipartFile;
import com.rttmall.shopbackend.app.gambling.pojo.VirtualData;

public class ReadExcel {

	//总行数
    private int totalRows = 0;  
    //总条数
    private int totalCells = 0; 
    //获取总行数
    public int getTotalRows()  { return totalRows;} 
    //获取总列数
    public int getTotalCells() {  return totalCells;} 
    
    /**
     * 验证EXCEL文件
     * @param filePath
     * @return
     */
    public boolean validateExcel(String filePath){
          if (filePath == null || !(filePath.matches("^.+\\.(?i)(xls)$") || filePath.matches("^.+\\.(?i)(xlsx)$"))){  
              return false;  
          }  
          return true;
    }
    /**
     * 读EXCEL文件，获取客户信息集合
     * @param fielName
     * @return
     */
    public List<VirtualData> getExcelInfo(String fileName,CommonsMultipartFile cf){
         List<VirtualData> virtualDataList=new ArrayList<VirtualData>();
         //初始化输入流
         InputStream is = null;  
         try{
        	 File tmp = File.createTempFile("IMPORT_PRODUCT_", ".tmp");
             FileUtils.writeByteArrayToFile(tmp, cf.getBytes());
            //验证文件名是否合格
            if(!validateExcel(fileName)){
                return null;
            }
            //根据文件名判断文件是2003版本还是2007版本
            boolean isExcel2003 = true; 
            if(WDWUtil.isExcel2007(fileName)){
                isExcel2003 = false;  
            }
            //根据新建的文件实例化输入流
            is = new FileInputStream(tmp);
            //根据excel里面的内容读取客户信息
            virtualDataList = getExcelInfo(is, isExcel2003); 
            is.close();
        }catch(Exception e){
            e.printStackTrace();
        } finally{
            if(is !=null)
            {
                try{
                    is.close();
                }catch(IOException e){
                    is = null;    
                    e.printStackTrace();  
                }
            }
        }
        return virtualDataList;
    }
    /**
     * 根据excel里面的内容读取客户信息
     * @param is 输入流
     * @param isExcel2003 excel是2003还是2007版本
     * @return
     * @throws IOException
     */
    public  List<VirtualData> getExcelInfo(InputStream is,boolean isExcel2003){
         List<VirtualData> virtualDataList=null;
         try{
             /** 根据版本选择创建Workbook的方式 */
             Workbook wb = null;
             //当excel是2003时
             if(isExcel2003){
                 wb = new HSSFWorkbook(is); 
             }
             else{//当excel是2007时
                 wb = new XSSFWorkbook(is); 
             }
             //读取Excel里面客户的信息
             virtualDataList=readExcelValue(wb);
         }
         catch (IOException e)  {  
             e.printStackTrace();  
         } finally {
             IOUtils.closeQuietly(is);
         } 
         return virtualDataList;
    }
    /**
     * 读取Excel里面客户的信息
     * @param wb
     * @return
     */
    private List<VirtualData> readExcelValue(Workbook wb){ 
        //得到第一个shell  
         Sheet sheet=wb.getSheetAt(0);
         
        //得到Excel的行数
         this.totalRows=sheet.getPhysicalNumberOfRows();
         
        //得到Excel的列数(前提是有行数)
         if(totalRows>=1 && sheet.getRow(0) != null){
              this.totalCells=sheet.getRow(0).getPhysicalNumberOfCells();
         }
         
         List<VirtualData> virtualDataList=new ArrayList<VirtualData>();
         VirtualData virtualData;            
        //循环Excel行数,从第二行开始。标题不入库
         for(int r=1;r<totalRows;r++){
             Row row = sheet.getRow(r);
             if (row == null) continue;
             virtualData = new VirtualData();
             //循环Excel的列
             for(int c = 0; c <this.totalCells; c++){    
                 Cell cell = row.getCell(c);
                 if (null != cell){
                     if(c==0){
                    	 virtualData.setProductId(Integer.parseInt(cell.getStringCellValue()));
                     }else if(c==1){
                    	 virtualData.setVirtualKey(cell.getStringCellValue());
                     }else if(c==2){
                    	 virtualData.setSecret(cell.getStringCellValue());
                     }
                 }
             }
             virtualDataList.add(virtualData);
         }
         return virtualDataList;
    }
}
